﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DelonixRegiaSystem.Classes
{
    class BookingDBManager
    {
        public static ArrayList GetListOfGuest(string Name)
        {
            ArrayList resultList = new ArrayList();
            guest result = new guest();
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from guest where firstName like '%' + @Name + '%' "
                + " or lastName like '%' + @Name + '%'";
                comm.Parameters.AddWithValue("@Name", Name);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    result = new guest();
                    result.guestNo = (int)dr["GuestNo"];
                    result.firstName = (string)dr["FirstName"];
                    result.lastName = (string)dr["LastName"];
                    result.checkIn = (DateTime)dr["checkIn"];
                    if (dr["checkOut"] is DBNull)
                        result.checkOut = System.DateTime.Now;
                    else
                        result.checkOut = (DateTime)dr["checkOut"];
                    resultList.Add(result);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return resultList;
        } // Retrieve - guest[]
        public static guest GetGuestByGuestNo(int GuestNo)
        {
            guest result = new guest();
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from guest where GuestNo=@GuestNo";
                comm.Parameters.AddWithValue("@GuestNo", GuestNo);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    result.guestNo = (int)dr["GuestNo"];                                // 1. guestNo
                    result.firstName = (string)dr["FirstName"];                         // 2. firstName
                    result.lastName = (string)dr["LastName"];                           // 3. lastName
                    result.phoneNo = (string)dr["PhoneNo"];                             // 4. phoneNo
                    result.email = (string)dr["Email"];                                 // 5. email
                    result.address = (string)dr["Address"];                             // 6. address
                    result.postalCode = (string)dr["PostalCode"];                       // 7. postalCode
                    result.country = (string)dr["Country"];                             // 8. country
                    result.remarks = (string)dr["Remarks"];                             // 9. remarks
                    result.paymentMethod = (string)dr["PaymentMethod"];                 // 10. paymentMethod
                    result.checkIn = (DateTime)dr["CheckIn"];                           // 11. checkIn
                    result.desiredCheckOut = (DateTime)dr["DesiredCheckOut"];           // 12. desiredCheckOut
                    if (dr["CheckOut"] is DBNull)
                        result.checkOut = System.DateTime.Now;
                    else
                        result.checkOut = (DateTime)dr["CheckOut"];                     // 13. checkOut
                    result.typeOfBed = (string)dr["TypeOfBed"];                         // 14. typeOfBed
                    result.typeOfRoom = (string)dr["TypeOfRoom"];                       // 15. typeOfRoom
                    result.amountPaid = (int)dr["AmountPaid"];                          // 16. amountPaid
                    result.roomNum = (int)dr["RoomNum"];                             // 17. roomNo
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return result;
        } // Retrieve - guest
        public static int UpdateGuest(guest guest)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE guest SET firstName=@firstName, lastName=@lastName, phoneNo=@phoneNo, email=@email, "
                + "address=@address, postalCode=@postalCode, country=@country, remarks=@remarks, paymentMethod=@paymentMethod, "
                + "checkIn=@checkIn, desiredCheckOut=@desiredCheckOut, typeOfBed=@typeOfBed, typeOfRoom=@typeOfRoom, "
                + "amountPaid=@amountPaid, roomNum=@roomNum WHERE guestNo=@guestNo ";
                comm.Parameters.AddWithValue("@guestNo", guest.guestNo);                        // The fixed data
                comm.Parameters.AddWithValue("@firstName", guest.firstName);                    // 1. firstName
                comm.Parameters.AddWithValue("@lastName", guest.lastName);                      // 2. lastName
                comm.Parameters.AddWithValue("@phoneNo", guest.phoneNo);                        // 3. phoneNo
                comm.Parameters.AddWithValue("@email", guest.email);                            // 4. email
                comm.Parameters.AddWithValue("@address", guest.address);                        // 5. address
                comm.Parameters.AddWithValue("@postalCode", guest.postalCode);                  // 6. postalCode
                comm.Parameters.AddWithValue("@country", guest.country);                        // 7. country
                comm.Parameters.AddWithValue("@remarks", guest.remarks);                        // 8. remarks
                comm.Parameters.AddWithValue("@paymentMethod", guest.paymentMethod);            // 9. paymentMethod
                comm.Parameters.AddWithValue("@checkIn", guest.checkIn);                        // 10. checkIn
                comm.Parameters.AddWithValue("@desiredCheckOut", guest.desiredCheckOut);        // 11. desiredCheckOut
                comm.Parameters.AddWithValue("@typeOfBed", guest.typeOfBed);                    // 12. typeOfBed
                comm.Parameters.AddWithValue("@typeOfRoom", guest.typeOfRoom);                  // 13. typeOfRoom
                comm.Parameters.AddWithValue("@amountPaid", guest.amountPaid);                  // 14. amountPaid
                comm.Parameters.AddWithValue("@roomNum", guest.roomNum);                        // 15. roomNum
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        } // Update - guest
        public static int CheckOutGuest(guest guest)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE guest SET checkOut=@checkOut WHERE guestNo=@guestNo ";
                comm.Parameters.AddWithValue("@checkOut", guest.checkOut);
                comm.Parameters.AddWithValue("@guestNo", guest.guestNo);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        } // Update - guest
        public static int CreateGuest(guest guest)
        {
            int rowsinserted = 0;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO guest(firstName, lastName, phoneNo, email, address, postalCode, country, remarks, paymentMethod, "
                    + "checkIn, desiredCheckOut, typeOfBed, typeOfRoom, amountPaid, roomNum)"
                    + " VALUES (@firstName, @lastName, @phoneNo, @email, @address, @postalCode, @country, @remarks, @paymentMethod,"
                    + "@checkIn, @desiredCheckOut, @typeOfBed, @typeOfRoom, @amountPaid, @roomNum)";
                // Guest No is auto created in database, Check Out = null
                comm.Parameters.AddWithValue("@firstName", guest.firstName);                                // 1. firstName
                comm.Parameters.AddWithValue("@lastName", guest.lastName);                                  // 2. lastName
                comm.Parameters.AddWithValue("@phoneNo", guest.phoneNo);                                    // 3. phoneNo
                comm.Parameters.AddWithValue("@email", guest.email);                                        // 4. email
                comm.Parameters.AddWithValue("@address", guest.address);                                    // 5. address
                comm.Parameters.AddWithValue("@postalCode", guest.postalCode);                              // 6. postalCode
                comm.Parameters.AddWithValue("@country", guest.country);                                    // 7. country
                comm.Parameters.AddWithValue("@remarks", guest.remarks);                                    // 8. remarks
                comm.Parameters.AddWithValue("@paymentMethod", guest.paymentMethod);                        // 9. paymentMethod
                comm.Parameters.AddWithValue("@checkIn", guest.checkIn);                                    // 10. checkIn
                comm.Parameters.AddWithValue("@desiredCheckOut", guest.desiredCheckOut);                    // 11. checkOut
                comm.Parameters.AddWithValue("@typeOfBed", guest.typeOfBed);                                // 12. typeOfBed
                comm.Parameters.AddWithValue("@typeOfRoom", guest.typeOfRoom);                              // 13. typeOfRoom
                comm.Parameters.AddWithValue("@amountPaid", guest.amountPaid);                              // 14. amountPaid
                comm.Parameters.AddWithValue("@roomNum", guest.roomNum);                                    // 15. roomNum
                rowsinserted = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return rowsinserted;
        } // Create - guest
        public static int CreateCard(creditCard card)
        {
            int rowsinserted = 0;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO creditCard(cardNo, guestNo, firstName, lastName, cardExpire, cardSecurityNo)"
                + " VALUES(@cardNo, @guestNo, @firstName, @lastName, @cardExpire, @cardSecurityNo)";
                comm.Parameters.AddWithValue("@cardNo", card.cardNo);                                  // 1. cardNo
                comm.Parameters.AddWithValue("@guestNo", card.guestNo);                                // 2. guestNo
                comm.Parameters.AddWithValue("@firstName", card.cFirstName);                           // 3. firstName
                comm.Parameters.AddWithValue("@lastName", card.cLastName);                             // 4. lastName
                comm.Parameters.AddWithValue("@cardExpire", card.cardExpire);                          // 5. cardExpire
                comm.Parameters.AddWithValue("@cardSecurityNo", card.cardSecurityNo);                  // 6. cardSecurityNo
                rowsinserted = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return rowsinserted;
        } // Create - creditCard
        public static ArrayList GetListOfGuestCheckedInToday()
        {
            ArrayList resultList = new ArrayList();
            guest result = new guest();
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from guest where CONVERT(date, checkIn)=@checkIn";
                comm.Parameters.AddWithValue("@checkIn", System.DateTime.Today);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    result = new guest();
                    result.guestNo = (int)dr["GuestNo"];
                    result.firstName = (string)dr["FirstName"];
                    result.lastName = (string)dr["LastName"];
                    result.checkIn = (DateTime)dr["CheckIn"];
                    if (dr["CheckOut"] is DBNull)
                        result.checkOut = System.DateTime.Now;
                    else
                        result.checkOut = (DateTime)dr["CheckOut"];
                    result.roomNum = (int)dr["RoomNum"];
                    resultList.Add(result);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return resultList;
        } // Retrieve - guest[]
        public static ArrayList GetListOfGuestCheckedOutToday()
        {
            ArrayList resultList = new ArrayList();
            guest result = new guest();
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Select * from guest where CONVERT(date, checkOut)=@checkOut";
                comm.Parameters.AddWithValue("@checkOut", System.DateTime.Today);
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    result = new guest();
                    result.guestNo = (int)dr["GuestNo"];
                    result.firstName = (string)dr["FirstName"];
                    result.lastName = (string)dr["LastName"];
                    result.checkIn = (DateTime)dr["CheckIn"];
                    if (dr["CheckOut"] is DBNull)
                        result.checkOut = System.DateTime.Now;
                    else
                        result.checkOut = (DateTime)dr["CheckOut"];
                    result.roomNum = (int)dr["RoomNum"];
                    resultList.Add(result);
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return resultList;
        } // Retrieved - guest[]
        public static int CreateBooking(booking booking)
        {
            int rowsinserted = 0;
            try
            {
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO booking(guestNo, checkIn, roomNum, firstName, lastName)"
                + " VALUES(@guestNo, @checkIn, @roomNum, @firstName, @lastName)";
                comm.Parameters.AddWithValue("@guestNo", booking.guestNo);
                comm.Parameters.AddWithValue("@checkIn", booking.guestNo);
                comm.Parameters.AddWithValue("@roomNum", booking.guestNo);
                comm.Parameters.AddWithValue("@firstName", booking.guestNo);
                comm.Parameters.AddWithValue("@lastName", booking.guestNo);
                rowsinserted = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            return rowsinserted;
        } // Create - booking
        public static int UpdateBooking(booking booking)
        {
            int rowsupdated = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE booking SET checkOut=@checkOut WHERE guestNo=@guestNo ";
                comm.Parameters.AddWithValue("@guestNo", booking.guestNo);
                comm.Parameters.AddWithValue("@checkOut", booking.checkOut);
                rowsupdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsupdated;
        } // Update - booking
        public static int DeleteGuest(int guestNo)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM guest where guestNo=@guestNo";
                comm.Parameters.AddWithValue("@guestNo", guestNo);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        } // Delete - guest
        public static int DeleteCard(int guestNo)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM creditCard where guestNo=@guestNo";
                comm.Parameters.AddWithValue("@guestNo", guestNo);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        } // Delete - creditCard
        public static int DeleteBooking(int guestNo)
        {
            int rowsdeleted = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["DelonixDBConnectionString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM booking where guestNo=@guestNo";
                comm.Parameters.AddWithValue("@guestNo", guestNo);
                rowsdeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsdeleted;
        } // Delete - booking
    }
}

